package com.mp.approvalreq.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.sun.mail.util.MailSSLSocketFactory;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.*;
import javax.mail.Message.RecipientType;
import javax.mail.internet.*;
import java.io.File;
import java.util.List;
import java.util.Properties;

public class ExcelUtil {

    private static Logger logger = Logger.getLogger(ExcelUtil.class);

    private final static short black = IndexedColors.BLACK.getIndex();
    public static final int TIME_COLUMN_WIDTH = 2700;


    static Cell createCommonCell(Row row, int col, CellStyle style, String value) {
        Cell cell = row.createCell(col);//创建单元格
        cell.setCellStyle(style);
        cell.setCellValue(value);
        return cell;
    }

    static void createCommonCell(Row row, int col, CellStyle style, Double value) {
        Cell cell = row.createCell(col);//创建单元格
        cell.setCellStyle(style);
        cell.setCellValue(value);
    }


    /**
     * 是否是json字符
     *
     * @param str
     * @return
     */
    static boolean isJsonArrayStr(String str) {
        boolean flag = true;
        try {
            JSONArray arr = JSON.parseArray(str);
        } catch (Throwable e) {
            flag = false;
        }
        return flag;
    }

    /**
     * 垂直居中、水平左对齐
     * 评论左对齐方式
     *
     * @param wb
     * @param top
     * @param down
     * @param left
     * @param right
     * @return
     */
    static CellStyle createCommentLEFTStyle(Workbook wb, int top, int down, int left, int right) {
        return createCellStyle(wb, top, down, left, right, HorizontalAlignment.LEFT, VerticalAlignment.CENTER);
    }


    static CellStyle createCellStyle(Workbook wb, int top, int down,
                                     int left, int right) {
        return createCellStyle(wb, top, down, left, right, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
    }

    static CellStyle createCellStyle(Workbook wb, int top, int down,
                                     int left, int right, HorizontalAlignment ha, VerticalAlignment va) {
        CellStyle style = wb.createCellStyle();
        BorderStyle[] border = {BorderStyle.NONE, BorderStyle.THIN, BorderStyle.MEDIUM};
        style.setBorderTop(border[top]);
        style.setTopBorderColor(black);
        style.setBorderBottom(border[down]);
        style.setBottomBorderColor(black);
        style.setBorderLeft(border[left]);
        style.setLeftBorderColor(black);
        style.setBorderRight(border[right]);
        style.setRightBorderColor(black);

        style.setWrapText(true);
        // 垂直居中
        style.setAlignment(ha);
        // 水平居中
        style.setVerticalAlignment(va);
        return style;
    }

    static Font createFont(Workbook wb, String name, int size, boolean bold) {
        Font font = wb.createFont();
        font.setFontHeight((short) (size * 20));
        font.setFontName(name);
        font.setBold(bold);
        return font;
    }

    // downBorder: 0：没有,1:有边界线,2：加粗的边界线
    static void createRegion(Sheet sheet, int topIndex, int downIndex, int leftIndex,
                             int rightIndex, int topBorder, int downBorder, int leftBorder, int rightBorder) {

        CellRangeAddress cellRangeAddress = new CellRangeAddress(topIndex, downIndex, leftIndex, rightIndex);

        BorderStyle[] border = {BorderStyle.NONE, BorderStyle.THIN, BorderStyle.MEDIUM};

        RegionUtil.setTopBorderColor(black, cellRangeAddress, sheet);
        setBorderTop(border[topBorder], cellRangeAddress, sheet);

        RegionUtil.setBottomBorderColor(black, cellRangeAddress, sheet);
        setBorderBottom(border[downBorder], cellRangeAddress, sheet);

        RegionUtil.setLeftBorderColor(black, cellRangeAddress, sheet);
        setBorderLeft(border[leftBorder], cellRangeAddress, sheet);

        RegionUtil.setRightBorderColor(black, cellRangeAddress, sheet);
        setBorderRight(border[rightBorder], cellRangeAddress, sheet);

        sheet.addMergedRegion(cellRangeAddress);
    }


    static void createRegion(Sheet sheet, int top, int down, int left, int right) {
        createRegion(sheet, top, down, left, right, 1, 1, 1, 1);
    }


    static void setHeight(Row row, int height) {
        if (height > 0) {
            row.setHeight((short) (height / 5));
        }
    }

    private static void setBorderLeft(BorderStyle border, CellRangeAddress region,
                                      Sheet sheet) {
        int rowStart = region.getFirstRow();
        int rowEnd = region.getLastRow();
        int column = region.getFirstColumn();
        for (int i = rowStart; i <= rowEnd; i++) {
            Cell cell = CellUtil.getCell(CellUtil.getRow(i, sheet), column);
            CellUtil.setCellStyleProperty(cell, "borderLeft", border);
        }
    }

    private static void setBorderTop(BorderStyle border, CellRangeAddress region,
                                     Sheet sheet) {
        int colStart = region.getFirstColumn();
        int colEnd = region.getLastColumn();
        int rowIndex = region.getFirstRow();
        for (int i = colStart; i <= colEnd; i++) {
            Cell cell = CellUtil.getCell(CellUtil.getRow(rowIndex, sheet), i);  // 遍历区域中第一行所有单元格,让单元格内容相同
            CellUtil.setCellStyleProperty(cell, "borderTop", border);
        }
    }

    private static void setBorderBottom(BorderStyle border, CellRangeAddress region,
                                        Sheet sheet) {
        int colStart = region.getFirstColumn();
        int colEnd = region.getLastColumn();
        int rowIndex = region.getLastRow();
        for (int i = colStart; i <= colEnd; i++) {
            Cell cell = CellUtil.getCell(CellUtil.getRow(rowIndex, sheet), i);
            CellUtil.setCellStyleProperty(cell, "borderBottom", border);
        }
    }

    private static void setBorderRight(BorderStyle border, CellRangeAddress region,
                                       Sheet sheet) {
        int rowStart = region.getFirstRow();
        int rowEnd = region.getLastRow();
        int column = region.getLastColumn();

        for (int i = rowStart; i <= rowEnd; i++) {
            Cell cell = CellUtil.getCell(CellUtil.getRow(i, sheet), column);
            CellUtil.setCellStyleProperty(cell, "borderRight", border);
        }
    }

    public static void addComment(Cell cell, String string) {

        HSSFClientAnchor anchor = new HSSFClientAnchor(1, 2, 3, 4,
         (short) 0, 8, (short) 8, 0);
        HSSFComment comment = (HSSFComment) cell.getSheet().createDrawingPatriarch().createCellComment(anchor);
        comment.setString(new HSSFRichTextString(string));
        cell.setCellComment(comment);
    }

    static Row createRow(HSSFSheet sheet, int index) {
        return sheet.createRow(index);
    }


    public static void sendQQMail(String addressee, String title, String content, List<File> files) throws Throwable {
        final Properties props = new Properties();
        /*
         * 可用的属性： mail.store.protocol / mail.transport.protocol / mail.host /
         * mail.user / mail.from
         */
        // 表示SMTP发送邮件，需要进行身份验证
        props.put("mail.smtp.auth", "true");
        props.put("mail.smtp.host", "smtp.exmail.qq.com");
//        props.put("mail.smtp.port", "587");
        // 发件人的账号
//        props.put("mail.user", "935833932@qq.com");
//        // 访问SMTP服务时需要提供的密码
//        props.put("mail.password", "urzdsobtjazrbedg");
        props.put("mail.user", "service@managerplus.cn");
        // 访问SMTP服务时需要提供的密码
        props.put("mail.password", "f7StXBbzjrZxHPDs");  //  Managerplus123
        MailSSLSocketFactory sf = new MailSSLSocketFactory();
        sf.setTrustAllHosts(true);
        props.put("mail.smtp.ssl.enable", "true");
        props.put("mail.smtp.ssl.socketFactory", sf);
//        props.put("mail.password", "bfoggjomxslsbdbj");
//        // 表示SMTP发送邮件，需要进行身份验证
//        props.put("mail.smtp.auth", "true");
//        props.put("mail.smtp.host", "smtp.163.com");
//        // 发件人的账号
//        props.put("mail.user", "15270799881@163.com");
//        // 访问SMTP服务时需要提供的密码
//        props.put("mail.password", "15270799881mail");

        // 构建授权信息，用于进行SMTP进行身份验证
        Authenticator authenticator = new Authenticator() {
            @Override
            protected PasswordAuthentication getPasswordAuthentication() {
                // 用户名、密码
                String userName = props.getProperty("mail.user");
                String password = props.getProperty("mail.password");
                return new PasswordAuthentication(userName, password);
            }
        };
        // 使用环境属性和授权信息，创建邮件会话
        Session mailSession = Session.getInstance(props, authenticator);
        // 创建邮件消息
        MimeMessage message = new MimeMessage(mailSession);
        // 设置发件人
        InternetAddress from = new InternetAddress(
         props.getProperty("mail.user"));
        from.setPersonal("大管加", "UTF-8");
        message.setFrom(from);

        // 设置收件人
        InternetAddress to = new InternetAddress(addressee);
        message.setRecipient(RecipientType.TO, to);

        // 设置抄送
//        InternetAddress cc = new InternetAddress("luo_aaaaa@yeah.net");
//        message.setRecipient(RecipientType.CC, cc);

        // 设置密送，其他的收件人不能看到密送的邮件地址
//        InternetAddress bcc = new InternetAddress("aaaaa@163.com");
//        message.setRecipient(RecipientType.CC, bcc);

        // 设置邮件标题
        message.setSubject(title, "UTF-8");

        Multipart multipart = new MimeMultipart();
        BodyPart bodypart = new MimeBodyPart();
        bodypart.setContent(content, "text/html;charset=utf-8");
        multipart.addBodyPart(bodypart);

        if (files != null) {
            for (File file : files) {
                BodyPart attachmentBodyPart = new MimeBodyPart();
                DataSource source = new FileDataSource(file);
                attachmentBodyPart.setDataHandler(new DataHandler(source));

                // 网上流传的解决文件名乱码的方法，其实用MimeUtility.encodeWord就可以很方便的搞定
                // 这里很重要，通过下面的Base64编码的转换可以保证你的中文附件标题名在发送时不会变成乱码
                //sun.misc.BASE64Encoder enc = new sun.misc.BASE64Encoder();
                //messageBodyPart.setFileName("=?GBK?B?" + enc.encode(attachment.getName().getBytes()) + "?=");

                //MimeUtility.encodeWord可以避免文件名乱码
                // 截取出UUID拼装的符合,让用户看到文件名不含有这一串乱七八糟的字符串
                String fileName = file.getName().substring(0, file.getName().indexOf("_")) + ".xls";
                attachmentBodyPart.setFileName(MimeUtility.encodeWord(fileName));
                multipart.addBodyPart(attachmentBodyPart);
            }
        }

        // 设置邮件的内容体
        message.setContent(multipart);

        // 发送邮件
        Transport.send(message);
    }

}
